﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;

namespace Utility
{
    public class ListCommand
    {
        #region 构造函数

        public ListCommand()
        {
        }

        public ListCommand(string cmdText)
        {
            this._CmdText = cmdText;
        }

        public ListCommand(string cmdText, CommandType cmdType)
        {
            this._CmdText = cmdText;
            this._CmdType = cmdType;
        }

        public ListCommand(string cmdText, CommandType cmdType, DbParameter[] dbParameter)
        {
            this._CmdText = cmdText;
            this._CmdType = cmdType;
            this._DbParameter = dbParameter;
        }

        #endregion 构造函数

        #region 属性

        private string _CmdText;
        private CommandType _CmdType = CommandType.Text;
        private DbParameter[] _DbParameter = null;

        public string CmdText
        {
            get
            {
                return _CmdText;
            }
            set
            {
                if (!string.IsNullOrEmpty(value))
                    _CmdText = value;
            }
        }

        public CommandType CmdType
        {
            get
            {
                return _CmdType;
            }
            set
            {
                CmdType = value;
            }
        }

        public DbParameter[] DbParameter
        {
            get
            {
                return _DbParameter;
            }
            set
            {
                if (value != null)
                {
                    _DbParameter = value;
                }
            }
        }

        #endregion 属性
    }

    public class DbHelper : IDisposable
    {
        #region 属性

        public enum DbProviders
        {
            SQL,
            OLEDB,
            ODBC,
            ORACLE
        }

        /// <summary>
        /// SQL:"server=122.70.138.152;database=db_web401647;uid=web401647;pwd=xqaGQm2xcQ"  providerName="SQL"
        /// ACCESS OLEDB:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"/db/dbtest.mdb"  providerName="OLEDB";
        /// ACCESS ODBC:"Driver={Microsoft   Access   Driver   (*.mdb)};DBQ=F:\project\TyhGallery\db.mdb " providerName="ODBC"
        /// ORACLE:"data source=wzd;user=wzd;password=wzd;"providerName="ORACLE"
        /// Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=88.88.88.66)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=tpgas)));Persist Security Info=True;User id=thksoft; Password=thksoft" providerName="ORACLE"
        /// </summary>
        private string _ConnString = ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;

        private string _Provider = ConfigurationManager.ConnectionStrings["sqlProvider"].ConnectionString;

        private DbProviderFactory _factory;
        private bool _useTransaction = false;

        public string ConnString
        {
            get
            {
                return _ConnString;
            }
            set
            {
                if (!string.IsNullOrEmpty(value))
                {
                    _ConnString = value;
                }
            }
        }

        public bool Transaction
        {
            get
            {
                return _useTransaction;
            }
            set
            {
                _useTransaction = value;
            }
        }

        public string providerName
        {
            get
            {
                return _Provider;
            }
            set
            {
                if (!string.IsNullOrEmpty(value))
                {
                    _Provider = value;
                }
            }
        }

        #endregion 属性

        #region 构造函数

        public DbHelper()
        {
            CreateDbHelper();
        }

        public DbHelper(string name)
        {
            this._ConnString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
            string ProviderName = ConfigurationManager.ConnectionStrings[name].ProviderName;
            CreateDbHelper(ProviderName);
        }

        public DbHelper(string connectionString, string provider)
        {
            this._ConnString = connectionString;
            this._Provider = provider;
            CreateDbHelper();
        }

        public DbHelper(string connectionString, DbProviders provider)
        {
            this._ConnString = connectionString;
            CreateDbHelper(provider);
        }

        #endregion 构造函数

        #region 构造扩展函数

        private void CreateDbHelper()
        {
            this.Dispose();
            switch (_Provider)
            {
                case "SQL":
                    _factory = SqlClientFactory.Instance;
                    break;

                case "ORACLE":
                    _factory = Oracle.ManagedDataAccess.Client.OracleClientFactory.Instance;
                    break;

                case "OLEDB":
                    _factory = OleDbFactory.Instance;
                    break;

                case "ODBC":
                    _factory = OdbcFactory.Instance;
                    break;

                default:
                    _factory = SqlClientFactory.Instance;
                    break;
            }
        }

        private void CreateDbHelper(String ProviderName)
        {
            this.Dispose();
            switch (ProviderName)
            {
                case "System.Data.OracleClient":
                    _factory = Oracle.ManagedDataAccess.Client.OracleClientFactory.Instance;//  OracleClientFactory.Instance;
                    break;

                case "System.Data.SqlClient":
                    _factory = SqlClientFactory.Instance;
                    break;

                case "System.Data.OleDb":
                    _factory = OleDbFactory.Instance;
                    break;

                case "System.Data.Odbc":
                    _factory = OdbcFactory.Instance;
                    break;

                default:
                    _factory = SqlClientFactory.Instance;
                    break;
            }
        }

        private void CreateDbHelper(DbProviders provider)
        {
            this.Dispose();
            switch (provider)
            {
                case DbProviders.SQL:
                    _factory = SqlClientFactory.Instance;
                    break;

                case DbProviders.OLEDB:
                    _factory = OleDbFactory.Instance;
                    break;

                case DbProviders.ODBC:
                    _factory = OdbcFactory.Instance;
                    break;

                default:
                    _factory = SqlClientFactory.Instance;
                    break;
            }
        }

        #endregion 构造扩展函数

        #region CreateParameter

        public DbParameter CreateParameter(string parameterName, object value)
        {
            DbParameter parameter = _factory.CreateParameter();
            if (this._Provider.Equals("ORACLE") && parameterName.IndexOf('@') >= 0)
            {
                parameterName = parameterName.Replace("@", "");
            }
            parameter.ParameterName = parameterName;
            parameter.Value = value;
            return parameter;
        }

        public DbParameter CreateParameter(string parameterName, DbType dbtype, object value)
        {
            DbParameter parameter = _factory.CreateParameter();
            if (this._Provider.Equals("ORACLE") && parameterName.IndexOf('@') >= 0)
            {
                parameterName = parameterName.Replace("@", "");
            }
            parameter.DbType = dbtype;
            parameter.ParameterName = parameterName;
            parameter.Value = value;
            return parameter;
        }

        public DbParameter CreateParameter(string parameterName, object value, ParameterDirection parameterDirection)
        {
            DbParameter parameter = _factory.CreateParameter();
            if (this._Provider.Equals("ORACLE") && parameterName.IndexOf('@') >= 0)
            {
                parameterName = parameterName.Replace("@", "");
            }
            parameter.ParameterName = parameterName;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            return parameter;
        }

        public DbParameter CreateOutParameter(string parameterName, DbType type, int size)
        {
            DbParameter parameter = _factory.CreateParameter();
            if (this._Provider.Equals("ORACLE") && parameterName.IndexOf('@') >= 0)
            {
                parameterName = parameterName.Replace("@", "");
            }
            parameter.ParameterName = parameterName;
            parameter.DbType = type;
            parameter.Size = size;

            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }

        public DbParameter CreateParameter(string parameterName, DbType type, int size, object value)
        {
            DbParameter parameter = _factory.CreateParameter();
            if (this._Provider.Equals("ORACLE") && parameterName.IndexOf('@') >= 0)
            {
                parameterName = parameterName.Replace("@", "");
            }
            parameter.ParameterName = parameterName;
            parameter.DbType = type;
            parameter.Size = size;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }

        public DbParameter CreateParameter(string parameterName, DbType type, int size, object value, ParameterDirection parameterDirection)
        {
            DbParameter parameter = _factory.CreateParameter();
            if (this._Provider.Equals("ORACLE") && parameterName.IndexOf('@') >= 0)
            {
                parameterName = parameterName.Replace("@", "");
            }
            parameter.ParameterName = parameterName;
            parameter.DbType = type;
            parameter.Size = size;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            return parameter;
        }

        #endregion CreateParameter

        #region AddParameter

        private void AddParameters(DbParameter[] parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            _command.Parameters.Clear();
            if (parameters != null)
            {
                foreach (DbParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        _command.Parameters.Add(parameter);
                    }
                }
            }
        }

        private void AddParameters(ArrayList parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            _command.Parameters.Clear();
            if (parameters != null && parameters.Count > 0)
            {
                foreach (object parameter in parameters)
                {
                    if (parameter != null)
                    {
                        _command.Parameters.Add((DbParameter)parameter);
                    }
                }
            }
        }

        private void AddParameters(List<DbParameter> parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;
            _command.Parameters.Clear();
            if (parameters != null && parameters.Count > 0)
            {
                foreach (DbParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        _command.Parameters.Add(parameter);
                    }
                }
            }
        }

        public void AddParameter(string parameterName, object value)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;
            _command.Parameters.Add(CreateParameter(parameterName, value));
        }

        public void AddParameter(string parameterName, DbType dbtype, object value)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;
            _command.Parameters.Add(CreateParameter(parameterName, dbtype, value));
        }

        public void AddOutParameter(string parameterName, DbType type, int size)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;
            _command.Parameters.Add(CreateOutParameter(parameterName, type, size));
        }

        #endregion AddParameter

        #region ExecuteNonQuery

        public int ExecuteNonQuery(string strSQL)
        {
            return this.ExecuteNonQuery(strSQL, CommandType.Text, null);
        }

        public int ExecuteNonQuery(string strSQL, DbParameter[] parameters)
        {
            return this.ExecuteNonQuery(strSQL, CommandType.Text, parameters);
        }

        public int ExecuteNonQuery(string strSQL, List<DbParameter> parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            int i = -1;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
                this.AddParameters(parameters);
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                    _connection.Open();
                i = _command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return i;
        }

        public int ExecuteNonQuery(string strSQL, ArrayList parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            int i = -1;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                    _connection.Open();
                i = _command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return i;
        }

        public int ExecuteNonQuery(string strSQL, CommandType commandType, DbParameter[] parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            int i = -1;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters);
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                    _connection.Open();
                i = _command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return i;
        }

        #endregion ExecuteNonQuery

        #region ExecuteScaler

        public object ExecuteScaler(string strSQL)
        {
            return ExecuteScaler(strSQL, CommandType.Text, null);
        }

        public object ExecuteScaler(string strSQL, DbParameter[] parameters)
        {
            return ExecuteScaler(strSQL, CommandType.Text, parameters);
        }

        public object ExecuteScaler(string strSQL, ArrayList parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            object obj = null;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                obj = _command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return obj;
        }

        public object ExecuteScaler(string strSQL, List<DbParameter> parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            object obj = null;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                obj = _command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return obj;
        }

        public object ExecuteScaler(string strSQL, CommandType commandType, DbParameter[] parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            object obj = null;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters);
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                obj = _command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return obj;
        }

        #endregion ExecuteScaler

        #region ExecuteReader

        public DbDataReader ExecuteReader(string strSQL)
        {
            return this.ExecuteReader(strSQL, CommandType.Text, null);
        }

        public DbDataReader ExecuteReader(string strSQL, CommandType commandType)
        {
            return this.ExecuteReader(strSQL, commandType, null);
        }

        public DbDataReader ExecuteReader(string strSQL, List<DbParameter> parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DbDataReader reader = null;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();
            }
            return reader;
        }

        public DbDataReader ExecuteReader(string strSQL, ArrayList parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DbDataReader reader = null;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();
            }
            return reader;
        }

        public DbDataReader ExecuteReader(string strSQL, CommandType commandType, DbParameter[] parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DbDataReader reader = null;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters);
            }
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();
            }
            return reader;
        }

        #endregion ExecuteReader

        #region ExecuteDataSet

        public DataSet ExecuteDataSet(string strSQL)
        {
            return ExecuteDataSet(strSQL, CommandType.Text, null);
        }

        public DataSet ExecuteDataSet(string strSQL, DbParameter[] parameters)
        {
            return ExecuteDataSet(strSQL, CommandType.Text, parameters);
        }

        public DataSet ExecuteDataSet(string strSQL, CommandType commandType)
        {
            return ExecuteDataSet(strSQL, commandType, null);
        }

        /// <summary>
        ///  ArryList(DbParameter) dbhelper.CreateParameter("@N_DEV_ID", 10000)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="parameters">ArryList</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string strSQL, ArrayList parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DataSet ds = new DataSet();
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            adapter.SelectCommand = _command;
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }

                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }

            return ds;
        }

        /// <summary>
        ///  List(DbParameter) dbhelper.CreateParameter("@N_DEV_ID", 10000)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="parameters"> List(DbParameter)</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string strSQL, List<DbParameter> parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DataSet ds = new DataSet();
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
            }
            adapter.SelectCommand = _command;
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }

                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }

            return ds;
        }

        /// <summary>
        ///  DbParameter[] dbhelper.CreateParameter("@N_DEV_ID", 10000)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="parameters">数组DbParameter </param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string strSQL, CommandType commandType, DbParameter[] parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DataSet ds = new DataSet();
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters);
            }
            adapter.SelectCommand = _command;
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }

                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }

            return ds;
        }

        #endregion ExecuteDataSet

        #region ExecuteDataTable

        public DataTable ExecuteDataTable(string strSQL)
        {
            return this.ExecuteDataTable(strSQL, CommandType.Text, null, null);
        }

        public DataTable ExecuteDataTable(string strSQL, DbParameter[] parameters)
        {
            return this.ExecuteDataTable(strSQL, CommandType.Text, parameters, null);
        }

        public DataTable ExecuteDataTable(string strSQL, string tableName)
        {
            return this.ExecuteDataTable(strSQL, CommandType.Text, null, tableName);
        }

        public DataTable ExecuteDataTable(string strSQL, CommandType commandType, string tableName)
        {
            return this.ExecuteDataTable(strSQL, commandType, null, tableName);
        }

        public DataTable ExecuteDataTable(string strSQL, CommandType commandType)
        {
            return this.ExecuteDataTable(strSQL, commandType, null, null);
        }

        public DataTable ExecuteDataTable(string strSQL, CommandType commandType, DbParameter[] parameters)
        {
            return this.ExecuteDataTable(strSQL, commandType, parameters, null);
        }

        public DataTable ExecuteDataTable(string strSQL, ArrayList parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DataTable dt = new DataTable(); ;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }

            DbDataAdapter adapter = _factory.CreateDataAdapter();

            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
                //this.AddParameters(parameters);
            }
            adapter.SelectCommand = _command;
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }

                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }

            return dt;
        }

        public DataTable ExecuteDataTable(string strSQL, List<DbParameter> parameters)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DataTable dt = new DataTable(); ;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }

            DbDataAdapter adapter = _factory.CreateDataAdapter();
            _command.CommandText = strSQL;
            _command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters.ToArray());
                this.AddParameters(parameters);
            }
            adapter.SelectCommand = _command;
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }

                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return dt;
        }

        public DataTable ExecuteDataTable(string strSQL, CommandType commandType, DbParameter[] parameters, string tableName)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            DataTable dt;
            if (this._Provider.Equals("ORACLE") && strSQL.IndexOf('@') >= 0)
            {
                strSQL = strSQL.Replace("@", ":");
            }
            if (string.IsNullOrEmpty(tableName))
            {
                dt = new DataTable();
            }
            else
            {
                dt = new DataTable(tableName);
            }
            DbDataAdapter adapter = _factory.CreateDataAdapter();

            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters);
            }
            adapter.SelectCommand = _command;
            try
            {
                if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }

                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return dt;
        }

        #endregion ExecuteDataTable

        #region 批量执行的方法

        public bool ExecuteTransaction(List<ListCommand> listCmd)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbTransaction DbTrans = _connection.BeginTransaction();

            try
            {
                foreach (ListCommand cmd in listCmd)
                {
                    _command.CommandType = cmd.CmdType;
                    _command.CommandText = cmd.CmdText;
                    _command.Transaction = DbTrans;

                    if (cmd.DbParameter != null)
                    {
                        //_command.Parameters.Clear();
                        //_command.Parameters.AddRange(parameters);
                    }

                    _command.ExecuteNonQuery();
                }

                DbTrans.Commit();
                return true;
            }
            catch
            {
                DbTrans.Rollback();
                return false;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        public bool ExecuteSqlTran(string SqlList)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            string[] str = SqlList.Split(';');

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbTransaction DbTrans = _connection.BeginTransaction();
            _command.Transaction = DbTrans;

            try
            {
                for (int n = 0; n < str.Length; n++)
                {
                    string strsql = str[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        _command.CommandText = strsql;
                        _command.ExecuteNonQuery();
                    }
                }
                DbTrans.Commit();
                return true;
            }
            catch
            {
                DbTrans.Rollback();
                return false;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        public bool ExecuteSqlTran(string SqlList, string operName)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            string[] str = SqlList.Split(';');

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbTransaction DbTrans = _connection.BeginTransaction();
            _command.Transaction = DbTrans;

            try
            {
                for (int n = 0; n < str.Length; n++)
                {
                    string strsql = str[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        _command.CommandText = strsql;
                        _command.ExecuteNonQuery();
                    }
                }
                DbTrans.Commit();
                return true;
            }
            catch
            {
                DbTrans.Rollback();
                return false;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        public int ExecuteSqlTran(List<String> SQLStringList)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbTransaction DbTrans = _connection.BeginTransaction();
            _command.Transaction = DbTrans;

            try
            {
                int count = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n];
                    if (strsql.Trim().Length > 1)
                    {
                        _command.CommandText = strsql;
                        count += _command.ExecuteNonQuery();
                    }
                }
                DbTrans.Commit();
                return count;
            }
            catch
            {
                DbTrans.Rollback();
                return 0;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        public void ExecuteSqlTran(Hashtable SQLStringList)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbTransaction DbTrans = _connection.BeginTransaction();

            try
            {
                foreach (DictionaryEntry myDE in SQLStringList)
                {
                    string cmdText = myDE.Key.ToString();
                    DbParameter[] cmdParms = (DbParameter[])myDE.Value;
                    PrepareCommand(_command, _connection, DbTrans, cmdText, cmdParms);
                    int val = _command.ExecuteNonQuery();
                    _command.Parameters.Clear();
                }
                DbTrans.Commit();
            }
            catch
            {
                DbTrans.Rollback();
                throw;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (DbParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public int ExecuteSql(string SQLString, string content)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbParameter myParameter = CreateParameter("@content", DbType.String);

            myParameter.Value = content;
            _command.Parameters.Add(myParameter);
            try
            {
                int rows = _command.ExecuteNonQuery();
                return rows;
            }
            catch
            {
                return 0;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        public object ExecuteSqlGet(string SQLString, string content)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbParameter myParameter = CreateParameter("@content", DbType.String);

            myParameter.Value = content;
            _command.Parameters.Add(myParameter);
            try
            {
                object obj = _command.ExecuteScalar();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            DbParameter myParameter = CreateParameter("@fs", DbType.SByte);

            myParameter.Value = fs;
            _command.Parameters.Add(myParameter);
            try
            {
                _connection.Open();
                int rows = _command.ExecuteNonQuery();
                return rows;
            }
            catch
            {
                return 0;
            }
            finally
            {
                _command.Parameters.Clear();

                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        #endregion 批量执行的方法

        #region 存储过程操作

        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader RunProcedureSQL(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(this._ConnString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommandSQL(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedureSQL(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(this._ConnString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommandSQL(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private SqlCommand BuildQueryCommandSQL(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public int RunProcedureSQL(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(this._ConnString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommandSQL(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private SqlCommand BuildIntCommandSQL(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommandSQL(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        #endregion 存储过程操作

        #region 存储过程操作

        /// <summary>
        /// 执行存储过程 返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleDataReader</returns>
        public OracleDataReader RunProcedureOracle(string storedProcName, IDataParameter[] parameters)
        {
            OracleConnection connection = new OracleConnection(this._ConnString);
            OracleDataReader returnReader;
            connection.Open();
            OracleCommand command = BuildQueryCommandOracle(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedureOracle(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (OracleConnection connection = new OracleConnection(this._ConnString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommandOracle(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand</returns>
        private OracleCommand BuildQueryCommandOracle(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public int RunProcedureOracle(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (OracleConnection connection = new OracleConnection(this._ConnString))
            {
                int result;
                connection.Open();
                OracleCommand command = BuildIntCommandOracle(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 OracleCommand 对象实例(用来返回一个整数值)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand 对象实例</returns>
        private OracleCommand BuildIntCommandOracle(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommandOracle(connection, storedProcName, parameters);
            command.Parameters.Add(new OracleParameter("ReturnValue",
                OracleType.Int32, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        #endregion 存储过程操作

        #region 不用存储分页 ACCESS 和 SQL 2000有用 其它效率低下

        /// <summary>
        /// 分页查询 过期 ACCESS 和 SQL 2000有用 其它效率低下
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="DataTable">反回DataSet集</param>
        /// <param name="Field">查询字段</param>
        /// <param name="WHERE">查询条件</param>
        /// <param name="order">排序方式</param>
        /// <param name="page">这是第几页</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="pagecount">总共分几页</param>
        /// <param name="recount">总记录数</param>
        /// <returns></returns>
        public DataSet PagerDataSet(string TableName, string DataTable, string Field, string WHERE, string order, int page, int pagesize, out int pagecount, out int recount)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            try
            {
                if (_connection.State == ConnectionState.Closed)
                {
                    _connection.Open();
                }

                //统计记录总数!
                string sqlcount = "SELECT count(1) FROM " + DataTable;

                if (!string.IsNullOrEmpty(WHERE))
                {
                    sqlcount += " WHERE " + WHERE;
                }
                _command.CommandText = sqlcount;
                recount = (int)_command.ExecuteScalar();
                //统计总页数
                pagecount = recount % pagesize == 0 ? recount / pagesize : recount / pagesize + 1;

                if (page > pagecount)
                {
                    page = pagecount;
                }

                string sql = "SELECT top " + pagesize + " " + Field + " FROM " + DataTable;
                string Nsql = "SELECT top " + pagesize * (page - 1) + " ID FROM " + DataTable;
                if (page <= 1)
                {
                    if (!string.IsNullOrEmpty(WHERE))
                    {
                        sql += " WHERE " + WHERE;
                    }
                    if (!string.IsNullOrEmpty(order))
                    {
                        sql += " ORDER BY " + order;
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(WHERE) && !string.IsNullOrEmpty(order))
                    {
                        Nsql += " WHERE " + WHERE + " ORDER BY  " + order;
                        sql += " WHERE ID NOT IN(" + Nsql + ") AND " + WHERE + " ORDER BY " + order;
                    }
                    else if (string.IsNullOrEmpty(WHERE) && !string.IsNullOrEmpty(order))
                    {
                        Nsql += " ORDER BY " + order;
                        sql += " WHERE ID NOT IN (" + Nsql + ")  ORDER BY  " + order;
                    }
                    else if (!string.IsNullOrEmpty(WHERE) && string.IsNullOrEmpty(order))
                    {
                        Nsql += " WHERE " + WHERE;
                        sql += " WHERE ID NOT IN (" + Nsql + ")  AND  " + WHERE;
                    }
                    else
                    {
                        sql += " WHERE ID NOT IN (" + Nsql + ") ";
                    }
                }
                return this.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
        }

        #endregion 不用存储分页 ACCESS 和 SQL 2000有用 其它效率低下

        #region GetSchema 获取数据库架构信息

        public string GetSchemaPrimaryKeys(string tableName)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            string PrimaryKey = "";
            if (_connection.State == System.Data.ConnectionState.Closed)
                _connection.Open();
            try
            {
                DbDataAdapter adapter = _factory.CreateDataAdapter();
                _command = _factory.CreateCommand();
                _command.Connection = _connection;
                _command.CommandText = "SELECT top 1 * FROM " + tableName;
                _command.CommandType = CommandType.Text;
                adapter.SelectCommand = _command;
                DataTable dt = new DataTable();
                adapter.FillSchema(dt, SchemaType.Source);
                if (dt.Rows.Count > 0)
                {
                    DataColumn[] DColumn = dt.PrimaryKey;
                    PrimaryKey = DColumn[0].ColumnName;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return PrimaryKey;
        }

        public DataTable GetSchemaTables(string tableName)
        {
            DbConnection _connection = _factory.CreateConnection();
            _connection.ConnectionString = this._ConnString;
            DbCommand _command = _factory.CreateCommand();
            _command.Connection = _connection;

            _command = _factory.CreateCommand();
            _command.Connection = _connection;
            DataTable DTGetSchema = null;
            if (_connection.State == System.Data.ConnectionState.Closed)
                _connection.Open();
            try
            {
                DTGetSchema = _connection.GetSchema("columns", new string[] { null, null, tableName });
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (_connection.State != System.Data.ConnectionState.Closed)
                {
                    _connection.Close();
                }
            }
            return DTGetSchema;
        }

        #endregion GetSchema 获取数据库架构信息

        #region Dispose

        public void Dispose()
        {
            GC.Collect();
        }

        ~DbHelper()
        {
            Dispose();
        }

        #endregion Dispose
    }
}